#drafted by Jeremy VanDerWal ( jjvanderwal@gmail.com ... www.jjvanderwal.com )
#GNU General Public License .. feel free to use / distribute ... no warranties

################################################################################
library(RODBC)
setwd('E:/Jeremy Work Directory/craig/') 

newDB = odbcDriverConnect('Driver={MySQL ODBC 5.1 Driver};SERVER=mapserve.hpc.jcu.edu.au;Uid=mapusr;Pwd=mapusr;')

MVD = odbcDriverConnect('Driver={Microsoft Access Driver (*.mdb)};Dbq=\\\\Zl5570-jeremy\\MVD_Database\\MVD.mdb;SystemDB=\\\\Zl5570-jeremy\\MVD_Database\\Security.mdw;Uid=ctbcc;Pwd=admin;')


loc = read.csv('tt2.csv')
loc[,c('bc01','bc04','bc12','bc15')] = NA



tquery='SELECT base_pnts.east, base_pnts.north, bioclim.bc01, bioclim.bc04, bioclim.bc12, bioclim.bc15
FROM base_pnts INNER JOIN bioclim ON base_pnts.pnt_id = bioclim.pnt_id;'
ldata=sqlQuery(MVD,tquery)

for (ii in 1:nrow(loc)) {
	tdist = sqrt((ldata$east - loc$east[ii])^2 + (ldata$north - loc$north[ii])^2)
	loc[ii,c('bc01','bc04','bc12','bc15')] = ldata[which(tdist==min(tdist))[1],3:6]
}

###load the necessary libraries
library(SDMTools) 

#directory locations
work.dir = 'E:/Jeremy Work Directory/craig/' #define the directory with monthly tmin/tmax
setwd(work.dir)

################################################################################

#01.load the data
pchv = c(16,10,1,17,2,22)

tdata = read.csv ('Query40.csv')

png(paste(work.dir, 'craig.test.png',sep=''), width=14, height=14, units='cm', res=300, pointsize=5, bg='white')
par(mfrow=c(2,2), mar=c(5,5,2,2), oma=c(1,1,1,1))


plot(tdata$dem80,tdata$bc01, xlab='Elevation (m)', ylab='Annual mean temperature', xlim=c(0,1610),ylim=c(15,24), type='n', cex.lab=2, cex.axis=1.6, font.lab='2')
points(tdata$dem80[which(tdata$Subregion=="AU")], tdata$bc01[which(tdata$Subregion=="AU")], pch=19, col='#FFC12550')
points(tdata$dem80[which(tdata$Subregion=="CU")], tdata$bc01[which(tdata$Subregion=="CU")], pch=19, col='#4169E150')
points(tdata$dem80[which(tdata$Subregion=="SU")], tdata$bc01[which(tdata$Subregion=="SU")], pch=19, col='#008B4550')

#plot the locations
for (ii in 1:nrow(loc)){
points(loc$elevation[ii], loc$bc01[ii], pch=pchv[ii], cex=2.5, col='black')
}


legend('topright', c('Atherton (AU)','Carbine (CU)', 'Spec (SU)'), fill=c('#FFC12575','#4169E175','#008B4575'), title='Subregion', cex=1.7, bty='n')	

legend('bottomleft', legend=loc$site, pch=pchv, title='Site', cex=1.7, bty='n')	

plot(tdata$dem80,tdata$bc04, xlab='Elevation (m)', ylab='Temperature seasonality', xlim=c(0,1610),ylim=c(0.8,1.2),type='n', cex.lab=2, cex.axis=1.6, font.lab='2')
points(tdata$dem80[which(tdata$Subregion=="AU")], tdata$bc04[which(tdata$Subregion=="AU")], pch=19, col='#FFC12550')
points(tdata$dem80[which(tdata$Subregion=="CU")], tdata$bc04[which(tdata$Subregion=="CU")], pch=19, col='#4169E150')
points(tdata$dem80[which(tdata$Subregion=="SU")], tdata$bc04[which(tdata$Subregion=="SU")], pch=19, col='#008B4550')

#plot the locations
for (ii in 1:nrow(loc)){
points(loc$elevation[ii], loc$bc04[ii], pch=pchv[ii], cex=2.5,col='black')
}


plot(tdata$dem80,tdata$bc12, xlab='Elevation (m)', ylab='Annual precipitation', type='n', xlim=c(0,1610),cex.lab=2, cex.axis=1.6, font.lab='2')
points(tdata$dem80[which(tdata$Subregion=="AU")], tdata$bc12[which(tdata$Subregion=="AU")], pch=19, col='#FFC12550')
points(tdata$dem80[which(tdata$Subregion=="CU")], tdata$bc12[which(tdata$Subregion=="CU")], pch=19, col='#4169E150')
points(tdata$dem80[which(tdata$Subregion=="SU")], tdata$bc12[which(tdata$Subregion=="SU")], pch=19, col='#008B4550')

#plot the locations
for (ii in 1:nrow(loc)){
points(loc$elevation[ii], loc$bc12[ii], pch=pchv[ii], cex=2.5,col='black')
}


plot(tdata$dem80,tdata$bc15, xlab='Elevation (m)', ylab='Precipitation seasonality', xlim=c(0,1610),type='n', cex.lab=2, cex.axis=1.6, font.lab='2')
points(tdata$dem80[which(tdata$Subregion=="AU")], tdata$bc15[which(tdata$Subregion=="AU")], pch=19, col='#FFC12550')
points(tdata$dem80[which(tdata$Subregion=="CU")], tdata$bc15[which(tdata$Subregion=="CU")], pch=19, col='#4169E150')
points(tdata$dem80[which(tdata$Subregion=="SU")], tdata$bc15[which(tdata$Subregion=="SU")], pch=19, col='#008B4550')

#plot the locations
for (ii in 1:nrow(loc)){
points(loc$elevation[ii], loc$bc15[ii], pch=pchv[ii], cex=2.5,col='black')
}

dev.off()
